package me.zixing.memorandum.utils;

import me.zixing.memorandum.domain.DayDO;
import me.zixing.memorandum.domain.DayTask;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.*;

public class JdbcUtils {
    private static String userName = "root";
    private static String passwd = "000000";
    private static String url = "jdbc:mysql://localhost:3306/javacurriculum?useUnicode=true&characterEncoding=UTF-8&useSSL=false";
    private static List<Connection> connections = new ArrayList<>();
    private static int connectionInitCount = 3;
    static{
        try{
            Class.forName("com.mysql.jdbc.Driver");
            for (int i = 0;i<connectionInitCount;i++){
                Connection connection = DriverManager.getConnection(url,userName,passwd);
                connections.add(connection);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    private static Connection getConnection(){
        return connections.remove(0);
    }

    private static void backToPool(Connection connection){
        connections.add(connection);
    }

    /**
     * 查询某天的备忘
     * @return
     */
    public static List<DayTask> getDayTasks(DayDO dayDO){
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat allFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        SimpleDateFormat hourAndMinuteFormat =
                new SimpleDateFormat("HH:mm");
        String sql = "select * from memorandum where starttime >= ? and endtime <= ? ORDER BY starttime;";
        Connection connection = getConnection();
        List<DayTask> dayTasks = new ArrayList<>();
        try(
                PreparedStatement statement = connection.prepareStatement(sql)){
            statement.setString(1,format.format(dayDO.getDate())+" 00:00:00");
            statement.setString(2,format.format(dayDO.getDate())+" 23:59:59");
            ResultSet resultSet = statement.executeQuery();
            while(resultSet.next()){
                DayTask dayTask = new DayTask();
                dayTask.setId(resultSet.getInt(1));
                dayTask.setTitle(resultSet.getString(2));
                dayTask.setPriority(resultSet.getInt(3));
                String startDateTime = hourAndMinuteFormat.format(allFormat.parse(resultSet.getString(4)));
                dayTask.setStartDateTime(startDateTime);
                String endDateTime = hourAndMinuteFormat.format(allFormat.parse(resultSet.getString(5)));
                dayTask.setEndDateTime(endDateTime);
                dayTask.setState(System.currentTimeMillis() > allFormat.parse(resultSet.getString(4)).getTime() ? 1:0);
                dayTask.setContent(resultSet.getString(7));
                dayTask.setDayDO(dayDO);
                dayTasks.add(dayTask);
            }

            SimpleDateFormat YMDFormat = new SimpleDateFormat("yyyyMMdd");

            if(YMDFormat.format(dayDO.getDate()).equals(YMDFormat.format(new Date()))){
                for(DayTask dayTask:dayTasks){
                    if(dayTask.getState() == 0){
                        TimingUtils.registeDayTask(dayTask);
                    }
                }
            }

        }catch (Exception e){
            e.printStackTrace();
            return Collections.EMPTY_LIST;
        }finally {
            JdbcUtils.backToPool(connection);
        }
        return dayTasks;
    }

    /**
     * 查询过去几年的某天备忘
     * @param dayDO
     * @return
     */
    public static List<DayTask> getPostDayTasks(DayDO dayDO){

        SimpleDateFormat monthFormat = new SimpleDateFormat("MM");
        SimpleDateFormat dayFormat = new SimpleDateFormat("dd");

        String sql = "select * from memorandum WHERE MONTH(starttime) = ? and DAY(starttime) = ?" +
                " and MONTH(endtime) = ? and DAY(endtime) = ? ORDER BY starttime DESC;";
        Connection connection = getConnection();
        List<DayTask> dayTasks = new ArrayList<>();
        try(
                PreparedStatement statement = connection.prepareStatement(sql)){
            statement.setString(1,monthFormat.format(dayDO.getDate()));
            statement.setString(2,dayFormat.format(dayDO.getDate()));
            statement.setString(3,monthFormat.format(dayDO.getDate()));
            statement.setString(4,dayFormat.format(dayDO.getDate()));
            ResultSet resultSet = statement.executeQuery();
            while(resultSet.next()){
                DayTask dayTask = new DayTask();
                dayTask.setId(resultSet.getInt(1));
                dayTask.setTitle(resultSet.getString(2));
                dayTask.setPriority(resultSet.getInt(3));
                dayTask.setStartDateTime(resultSet.getString(4));
                dayTask.setEndDateTime(resultSet.getString(5));
                dayTask.setState(resultSet.getInt(6));
                dayTask.setContent(resultSet.getString(7));
                dayTask.setDayDO(dayDO);
                dayTasks.add(dayTask);
            }
        }catch (Exception e){
            e.printStackTrace();
            return Collections.EMPTY_LIST;
        }finally {
            JdbcUtils.backToPool(connection);
        }
        return dayTasks;
    }

    /**
     * 添加备忘
     * @param dayTask
     */
    public static DayTask addDayTask(DayTask dayTask){
        String sqlForInsert = "insert into memorandum(title,priority,starttime,endtime,state,content) VALUES(?,?,?,?,?,?);";
        String sqlForId = "select max(id) from memorandum;";
        Connection connection = getConnection();
        try(
                PreparedStatement statementInsert = connection.prepareStatement(sqlForInsert);
                PreparedStatement statementSelect = connection.prepareStatement(sqlForId);
                ){
            statementInsert.setString(1,dayTask.getTitle());
            statementInsert.setInt(2,dayTask.getPriority());
            statementInsert.setString(3,dayTask.getStartDateTime());
            statementInsert.setString(4,dayTask.getEndDateTime());
            statementInsert.setInt(5,dayTask.getState());
            statementInsert.setString(6,dayTask.getContent());
            statementInsert.execute();
            ResultSet resultSet = statementSelect.executeQuery();
            resultSet.next();
            int id = resultSet.getInt(1);
            dayTask.setId(id);
            return dayTask;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcUtils.backToPool(connection);
        }
        return null;
    }

    /**
     *
     * 删除备忘
     * @param dayTask
     */
    public static void deleteDayTask(DayTask dayTask){
        String sql = "delete from memorandum where id = ?";
        Connection connection = getConnection();
        try(
                PreparedStatement statement = connection.prepareStatement(sql);
        ){
            statement.setInt(1,dayTask.getId());
            statement.execute();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcUtils.backToPool(connection);
        }
    }

    /**
     * 更新备忘
     * @param dayTask
     */
    public static void updateDayTask(DayTask dayTask){
        Date date = dayTask.getDayDO().getDate();
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd ");
        String yearMonthDay = format.format(date);
        String sql = "update memorandum set id=?,title = ?,priority = ?,starttime = ?,endtime = ?,state = ?,content=? where id = ?";
        Connection connection = getConnection();
        try(
                PreparedStatement statement = connection.prepareStatement(sql);
        ){
            statement.setInt(1,dayTask.getId());
            statement.setString(2,dayTask.getTitle());
            statement.setInt(3,dayTask.getPriority());
            statement.setString(4,yearMonthDay+dayTask.getStartDateTime()+":00");
            statement.setString(5,yearMonthDay+dayTask.getEndDateTime()+":00");
            statement.setInt(6,dayTask.getState());
            statement.setString(7,dayTask.getContent());
            statement.setInt(8,dayTask.getId());

            statement.execute();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcUtils.backToPool(connection);
        }
    }
}
